const connection = require('../app/database')

class EventService {
	async create({
		title,
		description,
		start_date,
		end_date,
		club_id,
		background_image_url,
		registration_limit,
		status = 'pending'
	}) {
		const statement = `
      INSERT INTO 
        events (title, description, start_date, end_date, club_id,background_image_url, registration_limit,status)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)`
		const [result] = await connection.execute(statement, [
			title,
			description,
			start_date,
			end_date,
			club_id,
			background_image_url,
			registration_limit,
			status
		])
		return result
	}
	async remove(event_id) {
		const statement = `
      DELETE FROM events WHERE id = ?
    `
		const [result] = await connection.execute(statement, [event_id])
		return result
	}
	async update(
		event_id,
		{
			title,
			description,
			start_date,
			end_date,
			club_id,
			background_image_url,
			registration_limit
		}
	) {
		const statement = `
    UPDATE events
      SET title = ?, description = ?, start_date = ?, end_date = ?, club_id = ?, background_image_url = ?, registration_limit = ?
    WHERE id = ?
    `
		const [result] = await connection.execute(statement, [
			title,
			description,
			start_date,
			end_date,
			club_id,
			background_image_url,
			registration_limit,
			event_id
		])
		return result
	}
	// 条件查询全部
	async search({
		clubName,
		eventName,
		status,
		limit = 10,
		offset = 0
	}) {
		const queryParams = []
		const whereConditions = []

		if (clubName) {
			whereConditions.push(`c.name LIKE ?`)
			queryParams.push(`%${clubName}%`)
		}

		if (status) {
			whereConditions.push(`e.status = ?`)
			queryParams.push(status)
		} else {
		}

		if (eventName) {
			whereConditions.push(`e.title LIKE ?`)
			queryParams.push(`%${eventName}%`)
		}

		const whereClause = whereConditions.length
			? `WHERE ${whereConditions.join(' AND ')}`
			: ''

		const [events] = await connection.execute(
			`
      SELECT e.*, c.name AS club_name, c.club_avatar AS club_avatar
      FROM events e
      LEFT JOIN clubs c ON e.club_id = c.id
      ${whereClause}
      ORDER BY e.start_date DESC
      LIMIT ${limit} OFFSET ${offset}
    `,
			[...queryParams]
		)

		const [total] = await connection.execute(
			`
      SELECT COUNT(*) AS totalCount
      FROM events e
      LEFT JOIN clubs c ON e.club_id = c.id
      ${whereClause}
    `,
			queryParams
		)
		return {
			list: events,
			totalCount: total[0].totalCount
		}
	}
	// 更新状态
	async updateStatus(event_id, status) {
		const statement = `UPDATE events SET status = ? WHERE id = ?`
		const [res] = await connection.execute(statement, [status, event_id])
		return res
	}
	// 根据id查询
	async searchEventById(event_id) {
		const statement = `
			SELECT e.*, c.name AS club_name, c.club_avatar AS club_avatar
			FROM events e
			LEFT JOIN clubs c ON e.club_id = c.id
			WHERE e.id = ?
			ORDER BY e.start_date DESC
		`
		const [resuult] = await connection.execute(statement, [event_id])
		return resuult
	}
	// 查询参与的人员
	async searchEventMember(event_id) {
		console.log(event_id)
		const statement = `
			SELECT
				u.name,
				up.*
			FROM
				event_registrations er
			JOIN
				users u ON er.user_id = u.id
			JOIN
				user_profiles up ON u.id = up.user_id
			WHERE
				er.event_id = ?
		`
		const [result] = await connection.execute(statement, [event_id])
		return result
	}
	// 参加活动
	async joinEvent(event_id, user_id) {
		const setEvent = `
			UPDATE events SET registered_count = registered_count + 1 
			WHERE id = ?
		`
		await connection.execute(setEvent, [event_id])
		const insertEventMember = `
			INSERT INTO event_registrations(event_id, user_id) VALUES(?,?)
		`
		const [res] = await connection.execute(insertEventMember, [
			event_id,
			user_id
		])
		return res
	}
	// 退出活动
	async quitEvent(event_id, user_id) {
		const setEvent = `
			UPDATE events SET registered_count = registered_count - 1  
			WHERE id = ?
		`
		await connection.execute(setEvent, [event_id])
		const cancelEventMember = `
			DELETE FROM event_registrations
			WHERE user_id = ? AND event_id = ?
		`
		const [res] = await connection.execute(cancelEventMember, [
			user_id,
			event_id
		])
		console.log(res)
		return res
	}

	// 判断是否保过名
	async isRegister(event_id, user_id) {
		const statement = `
			SELECT * FROM event_registrations
			WHERE event_id = ? AND user_id = ?
		`
		const [result] = await connection.execute(statement, [event_id, user_id])
		return !!result.length
	}

	// 判断是否已经满人
	async isLimit(event_id) {
		const statement = `
			SELECT registration_limit, registered_count FROM events
			WHERE id = ?
		`
		const [result] = await connection.execute(statement, [event_id])
		return result
	}

	// 根据社团id查询活动
	async searchEventByClubId(club_id) {
		const statement = `
			SELECT * FROM events WHERE club_id = ?
		`
		const [result] = await connection.execute(statement, [club_id])
		return result
	}

	// 查询所有社团的活动个数
	async searchPublishEventCount() {
		const statement = `
			SELECT
				c.name,
				(SELECT COUNT(*) FROM events e WHERE c.id = e.club_id and e.status != 'rejected' and e.status != 'pending') as value
			FROM clubs c
		`
		const [result] = await connection.execute(statement)
		return result
	}
}

module.exports = new EventService()
